/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.model;

import com.db.SqlManager;
import com.exception.DataException;
import com.javabeans.Gestion;
import com.javabeans.Grupo;
import com.javabeans.Inscrito;
import com.javabeans.Materia;
import com.javabeans.Usuario;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 *
 * @author Administrador
 */
public class AdminInscrito implements InscritoManager{
    private Connection c ;
    public AdminInscrito(Connection c){
        this.c = c;
    }
    
    public int getIdInscrito() throws DataException {
        int res = 0;
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "SELECT IFNULL(max(IDINSCRITO)+1,1) AS ID FROM INSCRITO";
        try{
            preSt = c.prepareStatement(query);
            rs = preSt.executeQuery();
            rs.next();
            res = rs.getInt("ID");
        }catch(SQLException e){
            throw new DataException(" AdminInscrito-> getIdInscrito() "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return res;
    }

    public void saveInscrito(Inscrito i) throws DataException {
        PreparedStatement preSt = null;
        String query = "";
        try{
            query ="INSERT INTO INSCRITO VALUES("+i.getIdInscrito();
            query += ", "+i.getIdGestion();
            query += ", "+i.getIdGrupo();
            query += ", "+i.getIdPensum();
            query += ", "+i.getIdCarrera();
            query += ", "+i.getIdMateria();
            query += ", "+i.getIdEstudiante();
            query += ", '"+i.getFecha();
            query += "', "+i.getT1();
            query += ", "+i.getT2();
            query += ", "+i.getT3();
            query += ", "+i.getNota();
            query += ", "+i.getSeg2();
            query += ", "+i.getNf();
            query += ", "+i.isAprobado()+")";            
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
            
        }catch(SQLException ex){
            throw new DataException("Error! AdminInscrito -> saveInscrito(Inscrito i) "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
    public List<Inscrito> getEstadoInscripcionBasic(int idGestion, int idCarrera, int codSis) throws DataException {
        List<Inscrito> inscritos = new ArrayList<Inscrito>();
        ResultSet rs=null;
        PreparedStatement preSt=null;

        try{
            String  query = "SELECT IDINSCRITO";
                    query += ", IDGRUPO";
                    query += ", IDPENSUM";
                    query += ", IDMATERIA";
                    query += " FROM INSCRITO";
                    query += " WHERE IDGESTION="+idGestion+" AND IDCARRERA="+idCarrera+" AND IDESTUDIANTE="+codSis;            
            preSt=c.prepareStatement(query);
            rs=preSt.executeQuery();
            while(rs.next()){
                Inscrito i = new Inscrito();
                i.setIdInscrito(rs.getInt("IDINSCRITO"));
                i.setIdGestion(idGestion);
                i.setIdGrupo(rs.getInt("IDGRUPO"));
                i.setIdPensum(rs.getInt("IDPENSUM"));
                i.setIdCarrera(idCarrera);
                i.setIdMateria(rs.getInt("IDMATERIA"));
                i.setIdEstudiante(codSis);                
                inscritos.add(i);
            }
        }catch(SQLException ex){
            throw new DataException(" Error! AdminInscrito -> getEstadoInscripcionBasic(idGestion, idCarrera, codSis) "+ex.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return inscritos;
    }
    public List<Inscrito> getEstadoInscripPorGest(int idGestion, int idCarrera, int codSis) throws DataException{
        List<Inscrito> inscritos = new ArrayList<Inscrito>();
        ResultSet rs=null;
        PreparedStatement preSt=null;
        String query = "";
        try{
            query =  "SELECT I.IDINSCRITO, I.IDGRUPO";
            query += ", G.NRO_GRUPO";
            query += ", M.IDMATERIA";
            query += ", M.NOMBRE";
            query += ", M.SIGLA";
            query += ", CONCAT(U.NOMBRE,' ',U.APELLIDO) NOMBRE_DOCENTE";
            query += " FROM INSCRITO I";
            query += " INNER JOIN (GRUPO G INNER JOIN USUARIO U ON G.IDDOCENTE=U.IDUSUARIO)";
            query += " ON I.IDGESTION=G.IDGESTION AND I.IDGRUPO=G.IDGRUPO";
            query += " INNER JOIN MATERIA M ON I.IDCARRERA=M.IDCARRERA AND I.IDMATERIA=M.IDMATERIA";
            query += " WHERE I.IDGESTION="+idGestion+" AND I.IDCARRERA="+idCarrera;
            query += " AND I.IDESTUDIANTE="+codSis;
            query += " ORDER BY M.NOMBRE";            
            preSt=c.prepareStatement(query);
            rs=preSt.executeQuery();
            while(rs.next()){
                Inscrito i = new Inscrito();
                i.setIdInscrito(rs.getInt("IDINSCRITO"));
                i.setIdGestion(idGestion);
                i.setIdGrupo(rs.getInt("IDGRUPO"));
                i.setIdCarrera(idCarrera);
                i.setIdEstudiante(codSis);
                Materia m = new Materia();
                m.setIdMateria(rs.getInt("IDMATERIA"));
                m.setNombre(rs.getString("M.NOMBRE"));
                m.setSigla(rs.getString("M.SIGLA"));
                i.setMateria(m);
                Grupo g = new Grupo();
                g.setNroGrupo(rs.getInt("G.NRO_GRUPO"));
                Usuario u = new Usuario();
                u.setNombre(rs.getString("NOMBRE_DOCENTE"));
                g.setDocente(u);
                i.setGrupo(g);
                inscritos.add(i);
            }
        }catch(SQLException ex){
            throw new DataException(" Error! AdminInscrito -> getEstadoInscripPorGest(int idGestion, int idCarrera, int codSis) "+ex.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return inscritos;
    }

    public void deleteInscrito(int idInscrito) throws DataException {
        ResultSet rs = null;
        PreparedStatement preSt = null;
        String query = "";
        try{
            query = "DELETE FROM INSCRITO WHERE IDINSCRITO="+idInscrito;
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminInscrito-> deleteInscrito(idInscrito) query ====== "+query+" e = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }

    public void updateInscripto(int idInscrito, int idGrupo) throws DataException {
        PreparedStatement preSt = null;
        try{
            String query = "UPDATE INSCRITO SET IDGRUPO = "+idGrupo;
            query += " WHERE IDINSCRITO = "+idInscrito;
             System.out.println(query);
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException e){
            throw new DataException(" AdminInscrito -> updateInscripto(int idInscrito, int idGrupo) DataException = "+e.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
	
	public List<Inscrito> getKardex(int idCarrera, int idEstudiante) throws DataException{
        List<Inscrito> inscritos = new ArrayList<Inscrito>();
        ResultSet rs=null;
        PreparedStatement preSt=null;
        String query = "";
        try{
            query = " SELECT GE.YEAR";
            query += ", I.IDGESTION";
            query += ", G.NRO_GRUPO";
            query += ", I.IDMATERIA";
            query += ", MP.LEVEL";
            query += ", M.NOMBRE";
            query += ", M.SIGLA";
            query += ", I.T1";
            query += ", I.T2";
            query += ", I.T3";
            query += ", I.NOTA";
            query += ", I.2DA";
            query += ", I.NF";
            query += ", I.APROBADO";
            query += " FROM INSCRITO I";
            query += " INNER JOIN GRUPO G ON I.IDGESTION=G.IDGESTION AND I.IDGRUPO=G.IDGRUPO";
            query += " INNER JOIN GESTION GE ON I.IDGESTION=GE.IDGESTION";
            query += " INNER JOIN MATERIA_PENSUM MP ON I.IDPENSUM=MP.IDPENSUM AND I.IDCARRERA=MP.IDCARRERA AND I.IDMATERIA=MP.IDMATERIA";
            query += " INNER JOIN MATERIA M ON I.IDCARRERA=M.IDCARRERA AND I.IDMATERIA=M.IDMATERIA";
            query += " WHERE I.IDCARRERA="+idCarrera+" AND IDESTUDIANTE="+idEstudiante;
            query += " ORDER BY I.IDGESTION, M.NOMBRE";            
            preSt=c.prepareStatement(query);
            rs=preSt.executeQuery();
            while(rs.next()){
                Inscrito i = new Inscrito();
                i.setIdGestion(rs.getInt("I.IDGESTION"));
                i.setIdCarrera(idCarrera);
                i.setIdMateria(rs.getInt("I.IDMATERIA"));
                i.setIdEstudiante(idEstudiante);
                i.setT1(rs.getInt("I.T1"));
                i.setT2(rs.getInt("I.T2"));
                i.setT3(rs.getInt("I.T3"));
                i.setNota(rs.getInt("I.NOTA"));
                i.setSeg2(rs.getInt("I.2DA"));
                i.setNf(rs.getInt("I.NF"));
                i.setAprobado(rs.getBoolean("I.APROBADO"));
                Gestion ge = new Gestion();
                ge.setYear(rs.getInt("GE.YEAR"));
                i.setGestion(ge);
                Materia m = new Materia();
                m.setNombre(rs.getString("M.NOMBRE"));
                m.setSigla(rs.getString("M.SIGLA"));
                i.setMateria(m);
                Grupo g = new Grupo();
                g.setNroGrupo(rs.getInt("G.NRO_GRUPO"));
                i.setGrupo(g);
                i.setAux(rs.getString("MP.LEVEL"));
                inscritos.add(i);
            }
        }catch(SQLException ex){
            throw new DataException(" Error! AdminInscrito -> getKardex(int idCarrera, int idEstudiante) "+ex.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
        return inscritos;
    }

    public void updateNotasXLS(List<Inscrito> ins) throws DataException {
        ResultSet rs = null;
        PreparedStatement preSt = null;
        int i = 0;
        String query = "";
        try{
            for(Inscrito in : ins){
                query += "UPDATE INSCRITO SET T1="+in.getT1()+", T2="+in.getT2();
                query += ", T3="+in.getT3()+", NOTA="+in.getNota();
                query += ", 2DA="+in.getSeg2()+", NF="+in.getNf();
                query += ", APROBADO="+in.isAprobado();
                query += " WHERE IDGESTION="+in.getIdGestion();
                query += " AND IDGRUPO="+in.getIdGrupo();
                query += " AND IDPENSUM="+in.getIdPensum();
                query += " AND IDCARRERA="+in.getIdCarrera();
                query += " AND IDMATERIA="+in.getIdMateria();
                query += " AND IDESTUDIANTE="+in.getIdEstudiante();                
                preSt = c.prepareStatement(query);
                preSt.executeUpdate();
            }           
            
        }catch(SQLException e){
            throw new DataException("Error! AdminInscrito -> updateNotasXLS(List<Inscrito> in) "+e.getMessage());
        }finally{
            SqlManager.closeRs(rs);
            SqlManager.closePreSt(preSt);
        }
    }

    public void updateNotasXLS(Inscrito in) throws DataException {
        PreparedStatement preSt = null;
        try{           
            String query = "UPDATE INSCRITO SET T1="+in.getT1()+", T2="+in.getT2();
            query += ", T3="+in.getT3()+", NOTA="+in.getNota();
            query += ", 2DA="+in.getSeg2()+", NF="+in.getNf();
            query += ", APROBADO="+in.isAprobado();
            query += " WHERE IDGESTION="+in.getIdGestion();
            query += " AND IDGRUPO="+in.getIdGrupo();
            query += " AND IDPENSUM="+in.getIdPensum();
            query += " AND IDCARRERA="+in.getIdCarrera();
            query += " AND IDMATERIA="+in.getIdMateria();
            query += " AND IDESTUDIANTE="+in.getIdEstudiante();           
            preSt = c.prepareStatement(query);
            preSt.executeUpdate();
        }catch(SQLException ex){
            throw new DataException("Error! en AdminInscrito -> updateNotasXLS(Inscrito in) e = "+ex.getMessage());
        }finally{
            SqlManager.closePreSt(preSt);
        }
    }
}
